/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package UploadData;

import ConnectData.Config;
import ConnectData.getConnections;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.Vector;

public class Database {

    public Vector vtSubject;
    public Vector vtCourse;
    public Vector vtPayment;
    public Vector vtCertificate;
    public Vector vtMarks;
    public Vector vtUsers;
    public Vector vtStudents;
    public Vector vtCourseRegister;
    public Vector vtCourseDetail;
    public Vector vtvwPayment;

    public Vector loadUsers(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        String strSelect = "Select * from Users " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtUsers = new Vector();
            Vector vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtUsers.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:User don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtUsers;
    }

    public Vector loadUser(String strSearch) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        try {
            pst = cn.prepareCall(strSearch);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtUsers = new Vector();
            Vector vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtUsers.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:User don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtUsers;
    }

    public Vector loadSubject(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Vector vttem;
        ResultSetMetaData rsm = null;
        String strSelect = "Select * from Subject " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtSubject = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtSubject.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Subject don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtSubject;
    }

    public Vector loadSubjectDetail(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        String strSelect = "Select *from SubjectDetail " + str;
        Vector vttem;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtSubject = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtSubject.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Subject Detail don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtSubject;
    }

    public Vector loadCourse(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Vector vttem;
        ResultSetMetaData rsm = null;
        String strSelect = "select * from Course " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtCourse = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtCourse.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Speciality don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCourse;
    }

    public Vector loadPaymentType(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from PaymentType " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtPayment = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtPayment.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Payment Type don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtPayment;
    }

    public Vector loadPayment(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        try {
            String strSelect = "Select * from Payment " + str;
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtPayment = new Vector();
            Vector vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtPayment.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Payment don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtPayment;
    }

    public Vector loadCertificate(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        try {
            String strSelect = "Select * from Certificate " + str;
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtCertificate = new Vector();
            Vector vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtCertificate.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Certificate don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCertificate;
    }

    public Vector loadCourseRegister(String str) {
        Vector vtRow;
        Connection cn = new Config().cn;
        ResultSet rs = null;
        PreparedStatement pst = null;
        try {
            String strSelect = "Select * from CourseRegister " + str;
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            vtCourseRegister = new Vector();
            while (rs.next()) {
                vtRow = new Vector();
                vtRow.add(rs.getInt(1));
                vtRow.add(rs.getInt(2));
                vtRow.add(rs.getInt(3));
                vtRow.add(new Date(rs.getTimestamp("StartDate").getTime()));
                vtRow.add(new Date(rs.getTimestamp("EndDate").getTime()));
                vtRow.add(rs.getInt(6));
                vtCourseRegister.add(vtRow);
            }
            if (vtCourseRegister.size() == 0) {
                vtRow = new Vector();
                for (int i = 0; i < 6; i++) {
                    vtRow.add("null");
                }
                vtCourseRegister.add(vtRow);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:CourseRegister don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCourseRegister;
    }

    public Vector loadCourseReg(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from CourseRegister " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtCourseRegister = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtCourseRegister.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Marks don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCourseRegister;
    }

    public Vector loadMarks(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from Marks " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtMarks = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtMarks.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Marks don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtMarks;
    }

    public Vector loadStudents(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from Students " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtStudents = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtStudents.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Students don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtStudents;
    }

    public Vector loadStudent(String str) {
        Vector vtRow;
        Connection cn = new Config().cn;
        ResultSet rs = null;
        PreparedStatement pst = null;
        try {
            String strSelect = "Select * from Students " + str;
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            vtStudents = new Vector();
            while (rs.next()) {
                vtRow = new Vector();

                vtRow.add(rs.getInt(1));
                vtRow.add(rs.getString(2));
                vtRow.add(rs.getString(3));
                vtRow.add(rs.getInt(4));
                vtRow.add(rs.getString(5));
                vtRow.add(new Date(rs.getTimestamp("StuBirthday").getTime()));
                vtRow.add(rs.getString(7));
                vtRow.add(rs.getString(8));
                vtRow.add(rs.getString(9));
                vtRow.add(rs.getString(10));
                vtStudents.add(vtRow);
            }
            if (vtStudents.size() == 0) {
                vtRow = new Vector();
                for (int i = 0; i < 12; i++) {
                    vtRow.add("null");
                }
                vtStudents.add(vtRow);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Student don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtStudents;
    }

    public Vector loadvwCourseDetail(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from vwCourseDetail " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtCourseDetail = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtCourseDetail.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Course Detail don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCourseDetail;
    }

    public Vector loadvwPayment(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from vwPayment " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtvwPayment = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtvwPayment.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Payment don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtvwPayment;
    }

    public Vector loadvwCetificate(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from vwCertificate " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtCertificate = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtCertificate.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Certificate don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCertificate;
    }

    public Vector loadAVGMarkCertificate(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        try {
            pst = cn.prepareCall(str);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtCertificate = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtCertificate.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:Certificate don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCertificate;
    }

    public Vector loadvwCetificateCell(String str) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        Vector vttem;
        String strSelect = "select * from vwCertificateCell " + str;
        try {
            pst = cn.prepareCall(strSelect);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            vtCertificate = new Vector();
            vttem = new Vector();
            while (rs.next()) {
                Object obtemp = new Object();
                vttem = new Vector();
                for (int i = 1; i <= rsm.getColumnCount(); i++) {
                    String rsmtype = rsm.getColumnTypeName(i);
                    if (rsmtype.equalsIgnoreCase("varchar") || rsmtype.equalsIgnoreCase("nvarchar") || rsmtype.equalsIgnoreCase("nchar")) {
                        obtemp = rs.getString(i);
                    } else {
                        obtemp = rs.getObject(i);
                    }
                    if (obtemp == null) {
                        vttem.add("null");
                    } else {
                        vttem.add(obtemp.toString());
                    }
                    obtemp = new Object();
                }
                vtCertificate.add(vttem);
            }
        } catch (Exception ex) {
            System.err.println("Mistake 3:CertificateCell don't have data!");
            ex.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(rs);
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return vtCertificate;
    }

    public int addSubject(String newSbj) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtSub = loadSubject(" where SubName='" + newSbj + "' order by [SubName]");
        if (!newSbj.equals("") && (vtSub.size() <= 0)) {
            try {
                String strIns = "insert into Subject([SubName])values(?)";
                pst = cn.prepareCall(strIns);
                pst.setString(1, newSbj);
                n = pst.executeUpdate();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }

    public int addSubjectDetail(int subID, int CourseID) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtCount = loadSubjectDetail(" where SubID=" + subID + " and CourseID=" + CourseID);
        if (vtCount.size() <= 0) {
            try {
                String strIns = "insert into SubjectDetail(SubID,CourseID)values(?,?)";
                pst = cn.prepareCall(strIns);
                pst.setInt(1, subID);
                pst.setInt(2, CourseID);
                n = pst.executeUpdate();

            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }

    public int addCourse(String CourseName, float TotalFees) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtCount = loadCourse(" where CourseName='" + CourseName + "'");
        if ((vtCount.size() <= 0)) {
            try {
                String strIns = "insert into Course(CourseName,TotalFees)values(?,?)";
                pst = cn.prepareCall(strIns);
                pst.setString(1, CourseName);
                pst.setFloat(2, TotalFees);
                n = pst.executeUpdate();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }
     public int addCertificate(int CRID,float Mark) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtCount = loadCertificate(" where CRID="+CRID+" and Mark="+Mark);
        if ((vtCount.size() <= 0)) {
            try {
                String strIns = "insert into Certificate(CRID,Mark)values(?,?)";
                pst = cn.prepareCall(strIns);
                pst.setInt(1, CRID);
                pst.setFloat(2, Mark);
                n = pst.executeUpdate();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }
    public int addPaymentType(String TypeName) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtCount = loadPaymentType(" where TypeName='" + TypeName + "'");
        if ((vtCount.size() <= 0)) {
            try {
                String strIns = "insert into PaymentType(TypeName)values(?)";
                pst = cn.prepareCall(strIns);
                pst.setString(1, TypeName);
                n = pst.executeUpdate();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }

    public int addPayment(int PayTypeID, int CRID, float totalFees, float Paid, float Balance, int Times, int Status) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtCount = loadPayment(" where PayTypeID=" + PayTypeID + " and CRID=" + CRID + " and Times=" + Times);
        if (vtCount.size() <= 0) {
            try {
                String strIns = "insert into Payment(PayTypeID,CRID,TotalFees,Paid,Balance,Times,Status)values(?,?,?,?,?,?,?)";
                pst = cn.prepareCall(strIns);
                pst.setInt(1, PayTypeID);
                pst.setInt(2, CRID);
                pst.setFloat(3, totalFees);
                pst.setFloat(4, Paid);
                pst.setFloat(5, Balance);
                pst.setInt(6, Times);
                pst.setInt(7, Status);
                n = pst.executeUpdate();

            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }

    public int addMarks(int StuID, int SubID, float Mark,int Times,  int Status) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtCount = loadMarks(" where StuID=" + StuID + " and SubID=" + SubID+ " and Times=" + Times );
        if (vtCount.size() <= 0) {
            try {
                String strIns = "insert into Marks(StuID,SubID,Mark,Times,Status)values(?,?,?,?,?)";
                pst = cn.prepareCall(strIns);
                pst.setInt(1, StuID);
                pst.setInt(2, SubID);
                pst.setFloat(3, Mark);
                pst.setInt(4, Times);
                pst.setInt(5, Status);
                n = pst.executeUpdate();

            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }

    public int addStudents(Student stu) {
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        int n = 0;
        Vector vtCount = loadStudents(" where UserName='" + stu.getUserName() + "'");
        if (vtCount.size() <= 0) {
            try {
                String strIns = "insert into Students(UserName,StuPassword,StuName,StuBirthDay,StuGender,StuPhone,StuEmail,StuAddress)values(?,?,?,?,?,?,?,?)";
                pst = cn.prepareCall(strIns);
                pst.setString(1, stu.getUserName());
                pst.setString(2, stu.getStuPassword());
                pst.setString(3, stu.getStuName());
                pst.setString(4, stu.getStuBirthDay());
                pst.setBoolean(5, stu.getStuGender());
                pst.setString(6, stu.getStuPhone());
                pst.setString(7, stu.getStuEmail());
                pst.setString(8, stu.getStuAddress());
                n = pst.executeUpdate();

            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    getConnections.closeObject(pst);
                    getConnections.closeObject(cn);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return n;
    }

    public int UpdateStudent(Student stu) {

        int n = 0;
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        try {
            String strUpdata = "update Students set UserName=?,StuPassword=?, Status=?,StuName=?,StuBirthday=?,StuGender=?,StuPhone=?,StuEmail=?,StuAddress=? where StuID=?";
            pst = cn.prepareCall(strUpdata);
            pst.setString(1, stu.getUserName());
            pst.setString(2, stu.getStuPassword());
            pst.setInt(3, stu.getStatus());
            pst.setString(4, stu.getStuName());
            pst.setString(5, stu.getStuBirthDay());
            pst.setBoolean(6, stu.getStuGender());
            pst.setString(7, stu.getStuPhone());
            pst.setString(8, stu.getStuEmail());
            pst.setString(9, stu.getStuAddress());
            pst.setInt(10, stu.getStuID());
            n = pst.executeUpdate();
        } catch (Exception sQLException) {
            sQLException.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return n;
    }

    public int UpdateMark(int StuID, int SubID, float Mark, int Times, int Status) {
        int n = 0;
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        try {
            String strUpdata = "update Marks set Mark=?,Status=?  where SubID=? and StuID=? and Times=?";
            pst = cn.prepareCall(strUpdata);

            pst.setFloat(1, Mark);
            pst.setInt(2, Status);
            pst.setInt(3, SubID);
            pst.setInt(4, StuID);
            pst.setInt(5, Times);
            n = pst.executeUpdate();
        } catch (Exception sQLException) {
            sQLException.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return n;
    }
      public int UpdateMarkStatus(int StuID, int SubID) {
        int n = 0;
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        try {
            String strUpdata = "update Marks set Status=0  where SubID=? and StuID=?";
            pst = cn.prepareCall(strUpdata);

            pst.setInt(1, SubID);
            pst.setInt(2, StuID);
            n = pst.executeUpdate();
        } catch (Exception sQLException) {
            sQLException.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return n;
    }
    public int UpdateCourseRegister(int CRID, int Status) {

        int n = 0;
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        try {
            String strUpdata = "update CourseRegister set Status=? where CRID=?";

            pst = cn.prepareCall(strUpdata);

            pst.setInt(1, Status);
            pst.setInt(2, CRID);

            n = pst.executeUpdate();
        } catch (Exception sQLException) {
            sQLException.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return n;
    }
    public int UpdatePayment(int PayTypeID, int CRID, float totalFees, float Paid, float Balance, int Times, int Status) {
        int n = 0;
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        try {
            String strUpdata = "update Payment set PayTypeID=?,Paid=?,Balance=?,Status=?  where CRID=? and Times=?";
            pst = cn.prepareCall(strUpdata);
            pst.setInt(1, PayTypeID);
            pst.setFloat(2, Paid);
            pst.setFloat(3, Balance);
            pst.setInt(4, Status);
            pst.setInt(5, CRID);
            pst.setInt(6, Times);
            n = pst.executeUpdate();
        } catch (Exception sQLException) {
            sQLException.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return n;
    }

    public int DeleteMark(int StuID, int SubID, float Mark, int Times, int Status) {

        int n = 0;
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        try {
            String strUpdata = "delete from Marks where Mark=? and Status=? and SubID=? and StuID=? and Times=?";
            pst = cn.prepareCall(strUpdata);

            pst.setFloat(1, Mark);
            pst.setInt(2, Status);
            pst.setInt(3, SubID);
            pst.setInt(4, StuID);
            pst.setInt(5, Times);
            n = pst.executeUpdate();
        } catch (Exception sQLException) {
            sQLException.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return n;
    }

    public int DeletePayment(int PayTypeID, int CRID, float totalFees, float Paid, float Balance, int Times, int Status) {

        int n = 0;
        Connection cn = new Config().cn;
        PreparedStatement pst = null;
        try {
            String strUpdata = "delete from Payment where PayTypeID=? and Paid=? and Balance=? and Status=? and CRID=? and Times=?";
            pst = cn.prepareCall(strUpdata);
            pst.setInt(1, PayTypeID);
            pst.setFloat(2, Paid);
            pst.setFloat(3, Balance);
            pst.setInt(4, Status);
            pst.setInt(5, CRID);
            pst.setInt(6, Times);
            n = pst.executeUpdate();
        } catch (Exception sQLException) {
            sQLException.printStackTrace();
        } finally {
            try {
                getConnections.closeObject(pst);
                getConnections.closeObject(cn);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        return n;
    }
}
